package main

import (
	"database/sql"
	"fmt"
	"log"
	"os"
	"time"

	"gorm.io/driver/mysql"
	"gorm.io/gorm"
	"gorm.io/gorm/logger"
)

type User struct {
	ID           uint
	Name         string
	Email        *string
	Age          uint8
	Birthday     *time.Time
	MemberNumber sql.NullString
	ActivatedAt  sql.NullTime
	CreatedAt    time.Time
	UpdatedAt    time.Time
}

func main() {
	dsn := "root:root@tcp(192.168.1.11:3306)/gorm_test?charset=utf8mb4&parseTime=True&loc=Local"

	newLogger := logger.New(
		log.New(os.Stdout, "\r\n", log.LstdFlags), // io writer
		logger.Config{
			SlowThreshold:             time.Second, // Slow SQL threshold
			LogLevel:                  logger.Info, // Log level
			IgnoreRecordNotFoundError: true,        // Ignore ErrRecordNotFound error for logger
			Colorful:                  true,        // Disable color
		},
	)

	db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{
		Logger: newLogger,
	})
	if err != nil {
		panic(err)
	}

	//var user User
	//db.AutoMigrate(&user)

	// **************************** where条件查询需要记住 数据库的字段名称 ****************************
	//获取第一条匹配的记录
	//db.Where("name = ?", "john").First(&user)
	//SELECT * FROM `users` WHERE name = 'john' ORDER BY `users`.`id` LIMIT 1

	var users []User
	//查找全部匹配的记录
	//SELECT * FROM `users` WHERE name <> 'john'
	//db.Where("name <> ?", "john").Find(&users)

	//In
	//db.Where("name IN ? ", []string{"john", "james"}).Find(&users)
	//SELECT * FROM `users` WHERE name IN ('john','james')

	//LIKE
	//db.Where("name lIKE ? ", "%joh%").Find(&users)
	//SELECT * FROM `users` WHERE name lIKE '%joh%'

	// AND
	//SELECT * FROM `users` WHERE name = 'james' AND age >= 0
	//db.Where("name = ? AND age >= ?", "james", 0).Find(&users)

	//db.Where("updated_at > ?", util.GetDay(-6)).Find(&users)
	//db.Where("updated_at > ?", util.GetDayStr(-6)).Find(&users)

	//between
	//db.Where("created_at BETWEEN ? AND ?", util.GetDay(-6), util.GetDay(0)).Find(&users)
	//for _, u := range users {
	//	fmt.Println(u.Name)
	//}
	// **************************** where条件查询需要记住 数据库的字段名称 ****************************

	// **************************** 当使用结构体作为查询条件时，GORM只会查询非零字段，这意味着如果您的字段值为0、'' 、false或者是其他零值，该字段不会被用于构建查询条件 ****************************
	//db.Where(&User{Name: "彬少", Age: 0}).Find(&users)
	////SELECT * FROM `users` WHERE `users`.`name` = '彬少'

	//使用Map来构建条件，会使用所有的值
	db.Where(map[string]interface{}{"Name": "james", "Age": 0}).Find(&users)
	//SELECT * FROM `users` WHERE `Age` = 0 AND `Name` = 'james'
	for _, u := range users {
		fmt.Println(u.Name)
	}

	//*******************当使用结构体进行查询时，可以使用它的字段名或其dbname列名作为参数来指定查询的字段
	db.Where(&User{Name: "james"}, "name", "Age").Find(&users)
	//SELECT * FROM `users` WHERE `users`.`name` = 'james' AND `users`.`age` = 0
	for _, u := range users {
		fmt.Println(u.Name)
	}

}
